from src.main.server.mapper.CreateMysqlCon import CreateMysqlCon


class ServerFeedbackImpl:

    def __init__(self):
        self.con = CreateMysqlCon().get_con()
        if not self.con:
            raise ConnectionError("数据库连接失败，请检查配置或网络")
        self.cursor = self.con.cursor()

    def all_info(self):
        """查询所有反馈信息"""
        SQL = ("select sp.server_name, v.username, sf.status, sf.feedback "
               "from volunteers_info as v, server_project as sp, server_feedback as sf "
               "where sf.vol_id = v.ID and sf.server_name_id = sp.ID")

        self.cursor.execute(SQL)
        return self.cursor.fetchall()

    def get_info(self, data):
        SQL = ("""
               SELECT sp.server_name AS server_name,
                      v.username     AS username,
                      sf.status      AS status,
                      sf.feedback    AS feedback
               FROM server_feedback sf
                        JOIN server_project sp ON sf.server_name_id = sp.ID
                        JOIN volunteers_info v ON sf.vol_id = v.ID
               WHERE sp.server_name = %s
                 AND v.username = %s
               """)
        self.cursor.execute(SQL, data)
        return self.cursor.fetchall()

    def update(self, data):
        SQL = ("""
               update server_feedback sf
                   JOIN server_project sp ON sf.server_name_id = sp.ID
                   JOIN volunteers_info v ON sf.vol_id = v.ID
               set status      = %s,
                   feedback = %s
               where sp.server_name = %s
                 and v.username = %s
               """)
        self.cursor.execute(SQL, data)

    def insert(self,data):
        SQL = ("""
               INSERT INTO server_feedback (server_name_id, vol_id)
               SELECT sp.ID, v.ID
               FROM server_project sp
                        JOIN volunteers_info v ON v.username = %s AND sp.server_name = %s
               """)
        self.cursor.execute(SQL, data)

    def check(self, data):
        """确认数据库是否已经存在该数据
           保证唯一性
        """
        SQL = """
              SELECT count(*)
              FROM server_feedback sf
                       JOIN server_project sp ON sf.server_name_id = sp.ID
                       JOIN volunteers_info v ON sf.vol_id = v.ID
              WHERE v.username = %s
                AND sp.server_name = %s 
              """
        self.cursor.execute(SQL, data)
        return self.cursor.fetchall()


if __name__ == '__main__':
    print(ServerFeedbackImpl().check(("华子", "买鸡蛋")))